package com.migrator.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Properties;

/*
 * @author Tihack
 * 
 * @desc Classe abstraite representant une base de donnée et les traitements possibles qu on
 * pourrait faire
 */

public abstract class Database {
	
	/*Variables de connection */
	
	protected String host, username, password, port ,catalog;
	protected Connection conn;
	protected HashMap<Integer, String> mapType = new HashMap<Integer, String>() ;
        protected Boolean connected = false ;

	
	public abstract Collection<String> listCatalogs() ; 
	
	
	
	/* Retourne la connexion courante */
	
	
	public Connection getConnection() {
		return conn;
	}

	/* Permet de se connecter a une bdd */
	
	public String getCatalog() {
		return catalog;
	}
	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

        public Boolean isConnected(){
            return connected ;
        }
	public void connect(String driver, String url, Properties props) throws Exception{
		
			Class.forName(driver) ;
			conn = DriverManager.getConnection(url,props);
                        connected = true ;
			
	}
	
	/*ferme la connexion courante */
	
	public void close(){
		try {
			
			conn.close() ;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/*
	 * Execute les requetes sql devant renvoyer des données
	 */
	public ResultSet executeQuery(String sql){
		Statement state = null ;
		try {
			
			state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
			return state.executeQuery(sql) ;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null ;
		}
		
	}
	
	/*
	 * Execute les requetes sql ne devant rien renvoyer
	 */
	
	public void execute(String sql){
		Statement state = null;
		try {
			
			state = conn.createStatement() ;
			state.execute(sql);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/*
	 * Doit rendre la liste des Tables de la base de donnée
	 */
	public Collection<String> listTables(){
		
		Collection<String> result = new ArrayList<String>() ;
		ResultSet rs = null ;
		
		try {
			
			DatabaseMetaData dbdm = conn.getMetaData() ; // Recuperation des meta donnee de la base
			String[] types = {"TABLE"} ;
			
			
			
			rs = dbdm.getTables(null, null, "%", types) ;
			while(rs.next()){
				String str = rs.getString("TABLE_NAME") ;
				result.add(str) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					
					rs.close();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return result ;
	}
	
	/*
	 * Renvoie la liste des colonnes d une table
	 */
	public Collection<String> listColumns(String tablename){
		
		Collection<String> result  = new ArrayList<String>() ;
		ResultSet rs = null ;
		
		try {
			
			DatabaseMetaData dbmd = conn.getMetaData() ;
			rs = dbmd.getColumns(null, null, tablename, null);
			
			while (rs.next()) {
				result.add(rs.getString("COLUMN_NAME"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					
					rs.close();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return result ;
	}
	
 public PreparedStatement prepareStatement(String sql){
		
	 PreparedStatement statement = null;
	 
	 try {
		 
	     statement = conn.prepareStatement(sql);
	     
	 }catch (SQLException e){
	     e.printStackTrace() ;
	 }
	 return statement;
 }
 
 public boolean isNumeric(int type)
 {
   if (type == java.sql.Types.BIGINT || type == java.sql.Types.DECIMAL
       || type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT
       || type == java.sql.Types.INTEGER || type == java.sql.Types.NUMERIC
       || type == java.sql.Types.SMALLINT || type == java.sql.Types.TINYINT)
     return true;
   else
     return false;

 }

 public boolean isInteger(int type)
 {
   if (type == java.sql.Types.BIGINT || type == java.sql.Types.INTEGER || type == java.sql.Types.NUMERIC
       || type == java.sql.Types.SMALLINT || type == java.sql.Types.TINYINT-1 || type==java.sql.Types.BOOLEAN)
     return true;
   else
     return false;

 }

  public boolean isFloat(int type)
 {
   if ( type == java.sql.Types.DOUBLE|| type == java.sql.Types.FLOAT    )
     return true;
   else
     return false;

 }

   public boolean isDecimal(int type)
 {
   if ( type == java.sql.Types.DECIMAL    )
     return true;
   else
     return false;

 }


 public boolean isDate(int type)
 {
   if (type == java.sql.Types.DATE)
     return true;
   else
     return false;

 }
 public String generateCreate(String tableName){
	 
	 StringBuffer result = new StringBuffer();
	 
     try {
    	
    	 /*
    	  * Recuperation des informations sur la table
    	  */
    	StringBuffer sql = new StringBuffer();
    	sql.append("SELECT * FROM ");
    	sql.append(tableName) ;
    	ResultSet rs = executeQuery(sql.toString());
		ResultSetMetaData md = rs.getMetaData();
		
		result.append("CREATE TABLE ") ;
		result.append(tableName) ;
		result.append(" ( ");
		
		for(int i = 1 ; i <= md.getColumnCount() ; i++ ){
			
			if(i != 1 )
				result.append(',');
			result.append(md.getColumnName(i)) ;
			result.append(' ');
			
			if(md.isAutoIncrement(i))
				result.append(" serial ") ;
			
                        else  if (this.isInteger(md.getColumnType(i)))
                            result.append(" INTEGER ");
                        else  if (this.isFloat(md.getColumnType(i)))
                            result.append(" FLOAT ");
                        else  if (this.isDate(md.getColumnType(i)))
                            result.append(" DATE ");
                        else  if (md.getColumnTypeName(i).equalsIgnoreCase("DATETIME"))
                            result.append(" TIMESTAMP WITHOUT TIME ZONE ");
                         else{
                            System.out.println(" "+md.getColumnTypeName(i)+ " "+md.getColumnType(i)+" "+java.sql.Types.DOUBLE);
                                result.append(" "+md.getColumnTypeName(i)+ " ");
                                if ((md.getPrecision(i) <= 65535) && !(this.isDate(md.getColumnType(i))) )
                                {
                                  result.append('(');
                                  result.append(md.getPrecision(i));
                                  if (isDecimal(md.getColumnType(i)) && md.getScale(i) > 0)
                                  {
                                    result.append(',');
                                    result.append(md.getScale(i));
                                  }
                                  result.append(") ");
                                } else
                              result.append(' ');
                            }
                              if (md.isNullable(i) == ResultSetMetaData.columnNoNulls)
                                      result.append("NOT NULL ");
                              else
                              result.append("NULL ");

                            }

		DatabaseMetaData dbm = conn.getMetaData();
        ResultSet primary = dbm.getPrimaryKeys(null, null, tableName);
        
        boolean first = true;
        while (primary.next()){
          if (first)
          {
        	first = false;
        	result.append(',');
        	result.append("PRIMARY KEY(");
          } else
        	  result.append(",");

          result.append(primary.getString("COLUMN_NAME"));
        }

        if (!first)
        	result.append(')');

        result.append(" ); ");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return result.toString() ;
 }
 
 public String generateDrop(String table)
 {
   StringBuffer result = new StringBuffer();
   result.append("DROP TABLE ");
   result.append(table);
   result.append(";\n");
   return result.toString();
 }
 public boolean tableExists(String table) 
 {
   boolean result = false;
   ResultSet rs = null;
   
   try
   {
     DatabaseMetaData dbm = conn.getMetaData();

     String types[] = { "TABLE" };
     rs = dbm.getTables(null, null, table, types);
     result = rs.next();
     rs.close();
   } catch (SQLException e)
   {
     e.printStackTrace() ;
   }
   finally
   {
     if( rs!=null )
     {
       try
       {
         rs.close();
       } catch (SQLException e)
       {
       }
     }
   }    
   return result;
 }
 

}
